unit SuppliersForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBModule, Vcl.Grids, Vcl.DBGrids, IB,
  Vcl.StdCtrls, Vcl.Mask, Vcl.ExtCtrls, IBSQL, UserUtils, Vcl.DBCtrls;

type
  TfmSupplier = class(TForm)
    DBGrid1: TDBGrid;
    btnNew: TButton;
    btnDelete: TButton;
    btnSave: TButton;
    btnRefresh: TButton;
    btnClose: TButton;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label7: TLabel;
    Label6: TLabel;
    Label8: TLabel;
    txtCompanyName: TLabeledEdit;
    txtAddress: TLabeledEdit;
    txtPostCode: TLabeledEdit;
    txtTel: TMaskEdit;
    txtFax: TMaskEdit;
    txtContactHP: TMaskEdit;
    txtContactName: TLabeledEdit;
    txtContactEmail: TLabeledEdit;
    txtCompanyEmail: TLabeledEdit;
    Label9: TLabel;
    dbcbCountry: TDBLookupComboBox;
    dbcbState: TDBLookupComboBox;
    dbcbCity: TDBLookupComboBox;
    procedure FormCreate(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure btnRefreshClick(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
    procedure DBGrid1DblClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure dbcbCountryDropDown(Sender: TObject);
    procedure dbcbStateDropDown(Sender: TObject);
    procedure dbcbCityDropDown(Sender: TObject);
  private
    { Private declarations }
    Operation: TOperation;
    procedure RefreshDB;
  public
    { Public declarations }
  end;

var
  fmSupplier: TfmSupplier;

implementation

uses
  MainForm;

{$R *.dfm}

procedure TfmSupplier.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfmSupplier.btnDeleteClick(Sender: TObject);
var
  ForceDelete: Boolean;
begin
  with DataModule1.qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    SQL.Add('select * from EXPENSES where SUPPLIER_ID=:ID');
    Params.ParamByName('ID').AsString := DBGrid1.DataSource.DataSet.FieldByName('ID').AsString;
    if not Prepared then
      Prepare;
    Open;
    ForceDelete := True;
    if RecordCount > 0 then
      if MessageBox(Self.WindowHandle, 'This supplier has transaction, force delete? The transactions will be deleted also.', 'Warning', MB_YESNO) <> IDYES then
        ForceDelete := False;
    if ForceDelete then
      try
        try
          SQL.Clear;
          Params.Clear;
          SQL.Add('delete from SUPPLIER where ID=:ID');
          Params.ParamByName('ID').AsString := DBGrid1.DataSource.DataSet.FieldByName('ID').AsString;
          if not Prepared then
            Prepare;
          ExecSQL;
          Transaction.Commit;
        except
          on E: EIBInterBaseError do
          begin
            Transaction.Rollback;
            ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
          end;
        end;
      finally
        RefreshDB;
      end;
  end;
end;

procedure TfmSupplier.btnNewClick(Sender: TObject);
begin
  txtCompanyName.Text := '';
  txtAddress.Text := '';
  txtPostCode.Text := '';
  txtTel.Text := '';
  txtFax.Text := '';
  txtCompanyEmail.Text := '';
  txtContactName.Text := '';
  txtContactEmail.Text := '';
  txtContactHP.Text := '';
  Operation := opNew;
end;

procedure TfmSupplier.btnRefreshClick(Sender: TObject);
begin
  RefreshDB;
end;

procedure TfmSupplier.btnSaveClick(Sender: TObject);
begin
  if txtCompanyName.Text <> '' then begin
    with DataModule1.qrySQL do
    begin
      SQL.Clear;
      Params.Clear;
      try
        try
          case Operation of
            opNew: begin
              SQL.Add('insert into SUPPLIER ' +
              '(company_name, address, postcode, state, city, country, ' +
              'company_tel, company_fax, company_email, contact_name, ' +
              'contact_email, contact_TEL) values (:CompanyName, :Address, :PostCode, ' +
              ':State, :City, :Country, :CompanyTel, :CompanyFax, :CompanyEmail, ' +
              ':ContactName, :ContactEmail, :ContactTel)');
            end;
            opEdit: begin
              SQL.Add('update SUPPLIER set company_name=:CompanyName, address=:Address, ' +
                      'postcode=:PostCode, state=:State, city=:City, country=:Country, ' +
                      'company_tel=:CompanyTel, company_fax=:CompanyFax, ' +
                      'company_email=:CompanyEmail, contact_name=:ContactName, ' +
                      'contact_email=:ContactEmail, contact_TEL=:ContactTel ' +
                      'where ID=:ID');
              Params.ParamByName('ID').AsString := DBGrid1.DataSource.DataSet.FieldByName('ID').AsString;
            end;
          end;
          Params.ParamByName('CompanyName').AsString := txtCompanyName.Text;
          Params.ParamByName('Address').AsString := txtAddress.Text;
          Params.ParamByName('PostCode').AsString := txtPostCode.Text;
          Params.ParamByName('State').AsString := VarToStr(dbcbState.KeyValue);
          Params.ParamByName('City').AsString := VarToStr(dbcbCity.KeyValue);
          Params.ParamByName('Country').AsString := VarToStr(dbcbCountry.KeyValue);
          Params.ParamByName('CompanyTel').AsString := txtTel.Text;
          Params.ParamByName('CompanyFax').AsString := txtFax.Text;
          Params.ParamByName('CompanyEmail').AsString := txtCompanyEmail.Text;
          Params.ParamByName('ContactName').AsString := txtContactName.Text;
          Params.ParamByName('ContactEmail').AsString := txtContactEmail.Text;
          Params.ParamByName('ContactTel').AsString := txtContactHP.Text;
          ExecSQL;
          Transaction.Commit;
        except
          on E: EIBInterBaseError do
          begin
            Transaction.Rollback;
            ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
          end;
        end;
      finally
        RefreshDB;
        btnNew.Click;
      end;
    end;
  end else
    ShowMessage('Company Name is required.');
end;

procedure TfmSupplier.dbcbCityDropDown(Sender: TObject);
begin
  with dbcbCity.ListSource.DataSet do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmSupplier.dbcbCountryDropDown(Sender: TObject);
begin
  with dbcbCountry.ListSource.DataSet do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmSupplier.dbcbStateDropDown(Sender: TObject);
begin
  with dbcbState.ListSource.DataSet do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmSupplier.DBGrid1DblClick(Sender: TObject);
begin
  with DBGrid1.DataSource.DataSet do
  begin
    txtCompanyName.Text := FieldByName('COMPANY_NAME').AsString;
    txtAddress.Text := FieldByName('ADDRESS').AsString;
    txtPostCode.Text := FieldByName('POSTCODE').AsString;
    txtCompanyEmail.Text := FieldByName('COMPANY_EMAIL').AsString;
    txtTel.Text := FieldByName('COMPANY_TEL').AsString;
    txtFax.Text := FieldByName('COMPANY_FAX').AsString;
    txtContactName.Text := FieldByName('CONTACT_NAME').AsString;
    txtContactEmail.Text := FieldByName('CONTACT_EMAIL').AsString;
    txtContactHP.Text := FieldByName('CONTACT_TEL').AsString;
    dbcbCountry.KeyValue := FieldByName('COUNTRY').AsString;
    dbcbState.KeyValue := FieldByName('STATE').AsString;
    dbcbCity.KeyValue := FieldByName('CITY').AsString;
  end;
  Operation := opEdit;
end;

procedure TfmSupplier.DBGrid1TitleClick(Column: TColumn);
begin
  with DataModule1 do
  begin
    fbCustomers.IndexFieldNames := Column.FieldName;
  end;
end;

procedure TfmSupplier.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FreeAndNil(fmSupplier);
end;

procedure TfmSupplier.FormCreate(Sender: TObject);
begin
  RefreshDB;
  Operation := opNew;
end;

procedure TfmSupplier.RefreshDB;
begin
  with DataModule1.fbSupplier do
  begin
    if not Active then
      Open;
    First;
  end;
end;

end.
